package com.course.dao;

import com.course.entity.Student;
import com.course.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
    
    // 添加学生
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO students (student_number, username, password, name, gender, class_name, email, phone) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, student.getStudentId());
            pstmt.setString(2, student.getUsername());
            pstmt.setString(3, student.getPassword());
            pstmt.setString(4, student.getName());
            pstmt.setString(5, student.getGender());
            pstmt.setString(6, student.getClassName());
            pstmt.setString(7, student.getEmail());
            pstmt.setString(8, student.getPhone());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 根据用户名查找学生
    public Student findByUsername(String username) {
        String sql = "SELECT * FROM students WHERE username = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return extractStudentWithPasswordFromResultSet(rs);
            }
            return null; // Return null if no result
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 根据学号查找学生
    public Student findByStudentNumber(String studentNumber) {
        String sql = "SELECT * FROM students WHERE student_number = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, studentNumber);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return extractStudentWithPasswordFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 根据ID查找学生
    public Student findById(int id) {
        String sql = "SELECT * FROM students WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return extractStudentWithPasswordFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 更新学生信息
    public boolean updateStudent(Student student) {
        String sql = "UPDATE students SET name = ?, gender = ?, class_name = ?, email = ?, phone = ? " +
                    "WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getGender());
            pstmt.setString(3, student.getClassName());
            pstmt.setString(4, student.getEmail());
            pstmt.setString(5, student.getPhone());
            pstmt.setInt(6, student.getId());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 修改密码
    public boolean updatePassword(int studentId, String newPassword) {
        String sql = "UPDATE students SET password = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, newPassword);
            pstmt.setInt(2, studentId);
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    // 删除学生
    public boolean deleteStudent(int id) {
        Connection conn = null;
        PreparedStatement pstmtDeleteSelections = null;
        PreparedStatement pstmtDeleteStudent = null;
        try {
            conn = DBConnection.getConnection();
            conn.setAutoCommit(false); // Start transaction

            // First, delete related records from course_selections
            String sqlDeleteSelections = "DELETE FROM course_selections WHERE student_id=?";
            pstmtDeleteSelections = conn.prepareStatement(sqlDeleteSelections);
            pstmtDeleteSelections.setInt(1, id);
            pstmtDeleteSelections.executeUpdate();

            // Then, delete the student from students table
            String sqlDeleteStudent = "DELETE FROM students WHERE id=?";
            pstmtDeleteStudent = conn.prepareStatement(sqlDeleteStudent);
            pstmtDeleteStudent.setInt(1, id);
            boolean success = pstmtDeleteStudent.executeUpdate() > 0;

            conn.commit(); // Commit transaction
            return success;
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback(); // Rollback on error
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmtDeleteSelections != null) pstmtDeleteSelections.close();
                if (pstmtDeleteStudent != null) pstmtDeleteStudent.close();
                if (conn != null) {
                    conn.setAutoCommit(true); // Reset auto-commit
                    DBConnection.releaseConnection(conn);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 从ResultSet中提取学生信息 (包括密码)
    private Student extractStudentWithPasswordFromResultSet(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setStudentId(rs.getString("student_number"));
        student.setUsername(rs.getString("username"));
        student.setPassword(rs.getString("password"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setClassName(rs.getString("class_name"));
        student.setEmail(rs.getString("email"));
        student.setPhone(rs.getString("phone"));
        return student;
    }

    // 从ResultSet中提取学生信息（不包括密码）
    private Student extractStudentWithoutPasswordFromResultSet(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setStudentId(rs.getString("student_number"));
        student.setUsername(rs.getString("username"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setClassName(rs.getString("class_name"));
        student.setEmail(rs.getString("email"));
        student.setPhone(rs.getString("phone"));
        return student;
    }

    // 获取所有学生信息（不包括密码）
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            System.out.println("StudentDAO: Getting all students...");
            conn = DBConnection.getConnection();
            String sql = "SELECT id, student_number, username, name, gender, class_name, email, phone FROM students";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentId(rs.getString("student_number"));
                student.setUsername(rs.getString("username"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setClassName(rs.getString("class_name"));
                student.setEmail(rs.getString("email"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
                System.out.println("StudentDAO: Found student - " + student.getName());
            }
            System.out.println("StudentDAO: Total students found: " + students.size());
            
        } catch (SQLException e) {
            System.err.println("StudentDAO: Error getting all students!");
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return students;
    }

    // 根据学号、姓名、班级搜索学生
    public List<Student> searchStudents(String studentId, String studentName, String className) {
        List<Student> students = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBConnection.getConnection();
            StringBuilder sql = new StringBuilder("SELECT id, student_number, username, name, gender, class_name, email, phone FROM students WHERE 1=1");
            List<String> params = new ArrayList<>();

            if (studentId != null && !studentId.trim().isEmpty()) {
                sql.append(" AND student_number LIKE ?");
                params.add("%" + studentId + "%");
            }
            if (studentName != null && !studentName.trim().isEmpty()) {
                sql.append(" AND name LIKE ?");
                params.add("%" + studentName + "%");
            }
            if (className != null && !className.trim().isEmpty()) {
                sql.append(" AND class_name LIKE ?");
                params.add("%" + className + "%");
            }

            System.out.println("StudentDAO: Search SQL: " + sql.toString());
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setString(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();

            while (rs.next()) {
                students.add(extractStudentWithoutPasswordFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return students;
    }
} 